﻿using System;
using LRS.DTO;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace LRS.DAL
{
    public class dSalesOrder
    {
        public static List<eSalesOrder> GetOrders()
        {
            List<eSalesOrder> list = new List<eSalesOrder>();
            SqlDataReader reader = SqlHelper.ExecuteReader("select * from SalesOrder");
            while (reader.Read())
            {
                eSalesOrder order = new eSalesOrder();
                order.OnHold = Boolean.Parse(reader["OnHold"].ToString());
                order.SalesOrderID = Int32.Parse(reader["SalesOrderID"].ToString());
                order.SalesOrderNumber = reader["SalesOrderNumber"].ToString();
                order.CustomerID = Int32.Parse(reader["CustomerID"].ToString());
                order.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
                int id;
                Int32.TryParse(reader["GetEmployeeID"].ToString(), out id);
                order.GetEmployeeID = id;
                order.TotalAmount = float.Parse(reader["Total"].ToString());
                order.DateOfBill = DateTime.Parse(reader["DateOfBill"].ToString());
                order.DateOfReturn = DateTime.Parse(reader["DateOfReturn"].ToString());
                DateTime date;
                DateTime.TryParse(reader["ActualDateOfReturn"].ToString(), out date);
                order.ActualDateOfReturn = date;
                float fine;
                float.TryParse(reader["Fine"].ToString(), out fine);
                order.Fine = fine;
                list.Add(order);
            }
            reader.Close();
            return list;
        }
        public static eSalesOrder GetOrderByNumber(string OrderNumber)
        {
            SqlDataReader reader = SqlHelper.ExecuteReader("Select * from SalesOrder where SalesOrderNumber='" + OrderNumber + "'");
            reader.Read();
            if (!reader.HasRows)
                return null;
            eSalesOrder order = new eSalesOrder();
            order.DateOfBill = DateTime.Parse(reader["DateOfBill"].ToString());
            order.DateOfReturn = DateTime.Parse(reader["DateOfReturn"].ToString());
            order.CustomerID = Int32.Parse(reader["CustomerID"].ToString());
            order.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
            order.SalesOrderNumber = reader["SalesOrderNumber"].ToString();
            order.TotalAmount = float.Parse(reader["Total"].ToString());
            order.SalesOrderID = Int32.Parse(reader["SalesOrderID"].ToString());
            order.OnHold = Boolean.Parse(reader["OnHold"].ToString());
            int id;
            Int32.TryParse(reader["GetEmployeeID"].ToString(), out id);
            order.GetEmployeeID = id;
            DateTime date;
            DateTime.TryParse(reader["ActualDateOfReturn"].ToString(), out date);
            order.ActualDateOfReturn = date;
            float fine;
            float.TryParse(reader["Fine"].ToString(), out fine);
            order.Fine = fine;
            reader.Close();
            return order;
        }
        public static eSalesOrder GetOrderByID(int OrderID)
        {
            SqlDataReader reader = SqlHelper.ExecuteReader("Select * from SalesOrder where SalesOrderID=" + OrderID);
            reader.Read();
            if (!reader.HasRows)
                return null;
            eSalesOrder order = new eSalesOrder();
            order.SalesOrderID = Int32.Parse(reader["SalesOrderID"].ToString());
            order.DateOfBill = DateTime.Parse(reader["DateOfBill"].ToString());
            order.DateOfReturn = DateTime.Parse(reader["DateOfReturn"].ToString());
            order.CustomerID = Int32.Parse(reader["CustomerID"].ToString());
            order.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
            order.SalesOrderNumber = reader["SalesOrderNumber"].ToString();
            order.TotalAmount = float.Parse(reader["Total"].ToString());
            order.OnHold = Boolean.Parse(reader["OnHold"].ToString());
            int id;
            Int32.TryParse(reader["GetEmployeeID"].ToString(), out id);
            order.GetEmployeeID = id;
            DateTime date;
            DateTime.TryParse(reader["ActualDateOfReturn"].ToString(), out date);
            order.ActualDateOfReturn = date;
            float fine;
            float.TryParse(reader["Fine"].ToString(), out fine);
            order.Fine = fine;
            reader.Close();
            return order;
        }
        public static string InsertOrder(eSalesOrder order)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@CustomerID", order.CustomerID));
            parameters.Add(new SqlParameter("@EmployeeID", order.EmployeeID));
            parameters.Add(new SqlParameter("@DateOfBill", order.DateOfBill));
            parameters.Add(new SqlParameter("@DateOfReturn", order.DateOfReturn));
            parameters.Add(new SqlParameter("@Total", order.TotalAmount));
            parameters.Add(new SqlParameter("@OnHold", order.OnHold));
            SqlParameter ordernumber = new SqlParameter("@OrderNumber",  System.Data.SqlDbType.NVarChar, 10);
            ordernumber.Direction = System.Data.ParameterDirection.Output;
            parameters.Add(ordernumber);
            SqlHelper.ExecuteProcedure("spInsertSalesOrder", parameters);
            order.SalesOrderNumber = ordernumber.Value.ToString();
            order.SalesOrderID = Int32.Parse(order.SalesOrderNumber.Substring(1));
            string oNumber = ordernumber.Value.ToString();
            return oNumber;
        }
        public static void UpdateOrder(eSalesOrder order)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@GetEmployeeID", order.GetEmployeeID));
            parameters.Add(new SqlParameter("@ActualDateOfReturn", order.ActualDateOfReturn));
            parameters.Add(new SqlParameter("@Fine", order.Fine));
            parameters.Add(new SqlParameter("@OnHold", order.OnHold));
            parameters.Add(new SqlParameter("@SalesOrderID", order.SalesOrderID));
            SqlHelper.ExecuteNonQuery("Update SalesOrder set GetEmployeeID=@GetEmployeeID, ActualDateOfReturn=@ActualDateOfReturn, Fine=@Fine, OnHold=@OnHold WHERE SalesOrderID=@SalesOrderID", parameters); 
        }
        public static List<eSalesOrder> GetOrdersByKeyword(string keyword)
        {
            List<eSalesOrder> list = new List<eSalesOrder>();
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@keyword", keyword));
            SqlDataReader reader = SqlHelper.ExecuteReader("Select s.* from SalesOrder s join Customer c on " +
                "s.CustomerID=c.CustomerID where s.SalesOrderNumber like '%' + @keyword + '%' OR c.CustomerNumber like '%' + @keyword + '%' OR c.FirstName like '%' + @keyword + '%' OR c.LastName like '%' + @keyword +'%' Order By s.DateOfBill DESC", parameters);
            while (reader.Read())
            {
                eSalesOrder order = new eSalesOrder();
                order.OnHold = Boolean.Parse(reader["OnHold"].ToString());
                order.SalesOrderID = Int32.Parse(reader["SalesOrderID"].ToString());
                order.SalesOrderNumber = reader["SalesOrderNumber"].ToString();
                order.CustomerID = Int32.Parse(reader["CustomerID"].ToString());
                order.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
                int id;
                Int32.TryParse(reader["GetEmployeeID"].ToString(), out id);
                order.GetEmployeeID = id;
                order.TotalAmount = float.Parse(reader["Total"].ToString());
                order.DateOfBill = DateTime.Parse(reader["DateOfBill"].ToString());
                order.DateOfReturn = DateTime.Parse(reader["DateOfReturn"].ToString());
                DateTime date;
                DateTime.TryParse(reader["ActualDateOfReturn"].ToString(), out date);
                order.ActualDateOfReturn = date;
                float fine;
                float.TryParse(reader["Fine"].ToString(), out fine);
                order.Fine = fine;
                list.Add(order);
            }
            reader.Close();
            return list;
        }
        public static void DeleteOrder(int SalesOrderID)
        {
            dSalesOrderDetail.DeleteDetail(SalesOrderID);
            SqlHelper.ExecuteNonQuery("Delete from SalesOrder where SalesOrderID=" + SalesOrderID);
        }
    }

}
